package dao.impl;

import dao.ISupplierDao;
import jdbc_util.FirstApp;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import pojo.Product;
import pojo.Supplier;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class SupplierDaoImpl implements ISupplierDao {
    @Override
    public void addSupplier(String name) {
        QueryRunner qr=null;
        Connection conn=null;
        String sql="insert into supplier(name) values(?)";
        try{
            qr=new QueryRunner();
            conn= FirstApp.getConn();
            conn.setAutoCommit(false);
            qr.update(conn,sql,name);
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public void updateSupplier(String name,int id) {
        QueryRunner qr=null;
        Connection conn=null;
        String sql="update supplier set name=? where id=?";
        try{
            qr=new QueryRunner();
            conn= FirstApp.getConn();
            conn.setAutoCommit(false);
            qr.update(conn,sql,name,id);
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public void deleteSupplier(int id) {
        QueryRunner qr=null;
        Connection conn=null;
        String sql="delete from supplier where id=?";
        try{
            qr=new QueryRunner();
            conn= FirstApp.getConn();
            conn.setAutoCommit(false);
            qr.update(conn,sql,id);
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public Supplier findByName(String name) {
        QueryRunner qr=null;
        Connection conn=null;
        Supplier supplier=null;
        List<Product> productList=null;
        String sql="Select * from supplier where name=?";
        String sql2="Select * from product where supplier_id=?";
        try{
            qr=new QueryRunner();
            conn= FirstApp.getConn();
            conn.setAutoCommit(false);
            supplier=qr.query(conn,sql,new BeanHandler<>(Supplier.class),name);
            productList=qr.query(conn,sql2,new BeanListHandler<>(Product.class),supplier.getId());
            supplier.setProductList(productList);
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return supplier;
    }

    @Override
    public Supplier findByid(int id) {
        QueryRunner qr=null;
        Connection conn=null;
        Supplier supplier=null;
        List<Product> productList=null;
        String sql="Select * from supplier where id=?";
        String sql2="Select * from product where supplier_id=?";
        try{
            qr=new QueryRunner();
            conn= FirstApp.getConn();
            conn.setAutoCommit(false);
            supplier=qr.query(conn,sql,new BeanHandler<>(Supplier.class),id);
            productList=qr.query(conn,sql2,new BeanListHandler<>(Product.class),supplier.getId());
            supplier.setProductList(productList);
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return supplier;

    }

    @Override
    public List<Supplier> findAll() {
        QueryRunner qr=null;
        Connection conn=null;
        List<Supplier> suppliers=null;
        List<Product> productList=null;
        String sql="Select * from supplier";
        String sql2="Select * from product where supplier_id=?";
        try{
            qr=new QueryRunner();
            conn= FirstApp.getConn();
            conn.setAutoCommit(false);
            suppliers=qr.query(conn,sql,new BeanListHandler<>(Supplier.class));
            for(Supplier supplier:suppliers){
                productList=qr.query(conn,sql2,new BeanListHandler<>(Product.class),supplier.getId());
                supplier.setProductList(productList);
            }
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return suppliers;
    }
}
